import path from 'path';
import * as fs from 'fs';
import { parse } from 'csv-parse/sync';
import { getDbInstance, Device } from './sqlite-database';

const DATA_DIR = path.join(process.cwd(), 'excel', '06-21');

const DEVICE_TABLE_FILES = [
  '设备表-配管专业.csv',
  '设备表-电气专业.csv',
  '设备表-暖通专业.csv',
  '设备表-通信专业.csv',
  '设备表-仪表专业.csv',
];
const PARAMETER_FILES = {
    '对象命名表': '属性表-对象命名表.csv',
    '位置信息表': '属性表-位置信息表.csv',
    '基础参数表': '属性表-基础参数表.csv'
};
type ParameterTemplateName = keyof typeof PARAMETER_FILES;

const UNIT_DEFINITION_FILE = '计量单位定义表.csv';

// 缓存参数定义，避免重复读取文件
const parameterDefinitionsCache = new Map<string, any[]>();

function getParameterDefinitions(templateName: ParameterTemplateName): any[] {
    if (parameterDefinitionsCache.has(templateName)) {
        return parameterDefinitionsCache.get(templateName)!;
    }

    const filename = PARAMETER_FILES[templateName];
    if (!filename) {
        console.warn(`[Importer] No parameter file mapping found for template: ${templateName}`);
        return [];
    }

    const filePath = path.join(DATA_DIR, filename);
    if (!fs.existsSync(filePath)) {
        console.warn(`[Importer] Parameter file not found: ${filePath}`);
        return [];
    }

    let content = fs.readFileSync(filePath);
    // Handle BOM
    if (content[0] === 0xEF && content[1] === 0xBB && content[2] === 0xBF) {
        content = content.subarray(3);
    }
    
    const records = parse(content.toString('utf-8'), { columns: true, skip_empty_lines: true });
    parameterDefinitionsCache.set(templateName, records);
    return records;
}

function importMeasurementUnits() {
  const filePath = path.join(DATA_DIR, UNIT_DEFINITION_FILE);
    if (!fs.existsSync(filePath)) {
        console.warn(`[Importer] Measurement unit file not found: ${filePath}`);
        return;
    }
    let contentBuffer = fs.readFileSync(filePath);
    // Handle BOM
    if (contentBuffer[0] === 0xEF && contentBuffer[1] === 0xBB && contentBuffer[2] === 0xBF) {
        contentBuffer = contentBuffer.subarray(3);
    }

    // 不使用 columns: true，手动处理重复列名的问题
    const content = contentBuffer.toString('utf-8');
    const lines = content.split('\n').filter(line => line.trim() !== '');

    let unitCount = 0;
    let sortOrder = 1;

    // 跳过第一行（列名行），从第二行开始处理数据
    for (let i = 1; i < lines.length; i++) {
        const line = lines[i].trim();
        if (!line) continue;

        // 手动分割CSV行，处理可能的引号
        const columns = line.split(',').map(col => col.trim().replace(/^"(.*)"$/, '$1'));

        // 确保有8列数据（4对计量类名-计量单位）
        if (columns.length >= 8) {
            const pairs = [
                [columns[0], columns[1]], // 第1对
                [columns[2], columns[3]], // 第2对
                [columns[4], columns[5]], // 第3对
                [columns[6], columns[7]]  // 第4对
            ];

            for (const [categoryName, unitName] of pairs) {
                if (categoryName && unitName &&
                    categoryName.trim() !== '' && unitName.trim() !== '' &&
                    categoryName !== '计量类名' && categoryName !== '—' && unitName !== '—') {

                    // 确定单位类型
                    let unitType = '常用单位';
                    if (unitName.includes('/')) {
                        unitType = '导出单位';
                    } else if (['m', 'kg', 's', 'A', 'K', 'mol', 'cd'].includes(unitName)) {
                        unitType = '基本单位';
                    }

                    getDbInstance().insertMeasurementUnit({
                        category_name: categoryName.trim(),
                        unit_name: unitName.trim(),
                        unit_symbol: unitName.trim(),
                        unit_type: unitType,
                        conversion_factor: 1.0,
                        base_unit: unitName.trim(),
                        description: `${categoryName.trim()}的计量单位`,
                        is_active: true,
                        sort_order: sortOrder++
                    });
                    unitCount++;
                }
            }
        } else {
            console.warn(`[Importer] Line ${i + 1} has insufficient columns: ${columns.length}`);
        }
    }
    console.log(`[Importer] Imported ${unitCount} measurement units into SQLite.`);
}

async function importDevicesAndParameters() {
    let totalDevices = 0;
    const deviceCounters: Record<string, number> = {
        '配管专业': 1, '电气专业': 1, '暖通专业': 1, '通信专业': 1, '仪表专业': 1
    };
    
    for (const filename of DEVICE_TABLE_FILES) {
        const specialty = filename.replace('设备表-', '').replace('.csv', '');
        const filePath = path.join(DATA_DIR, filename);
        if (!fs.existsSync(filePath)) continue;

        let contentBuffer = fs.readFileSync(filePath);
        // Handle BOM
        if (contentBuffer[0] === 0xEF && contentBuffer[1] === 0xBB && contentBuffer[2] === 0xBF) {
            contentBuffer = contentBuffer.subarray(3);
        }
        const records = parse(contentBuffer.toString('utf-8'), { columns: true, skip_empty_lines: true });
        console.log(`[Importer] Parsed ${records.length} records from ${filename}`);

        for (const record of records) {
            if (!record['设备表及材料表中的实体对象名称'] || record['设备表及材料表中的实体对象名称'].trim() === '') continue;

            const specialtyPrefix = specialty.substring(0, 2).toUpperCase();
            const deviceNumber = (deviceCounters[specialty]++).toString().padStart(4, '0');
            const uniqueDeviceId = `${specialtyPrefix}-${deviceNumber}`;
            
            const deviceData: Omit<Device, 'id' | 'created_at' | 'updated_at'> = {
                device_id: uniqueDeviceId,
                name: record['设备表及材料表中的实体对象名称'],
                classification_name: record['对应的对象分类标准中的中文名称'] || '',
                classification_code: record['分类码'] || '',
                specialty: specialty,
                sequence_number: record['序号'] || '',
                naming_table_id: record['对象命名'] || '对象命名表',
                position_table_id: record['位置信息'] || '位置信息表',
                base_params_table_id: record['基础参数'] || '基础参数表',
                tech_spec_table_id: record['技术规格书'] || '',
                remarks: record['备注'] || ''
            };
            
            const newDevice = getDbInstance().insertDevice(deviceData);
            totalDevices++;

            if (newDevice && newDevice.id) {
                // 插入命名参数
                const namingParams = getParameterDefinitions('对象命名表');
                for (const paramDef of namingParams) {
                    if (paramDef['中文名称'] && paramDef['中文名称'].trim() !== '') {
                        getDbInstance().insertNamingParameter({
                            device_id: newDevice.id,
                            parameter_name: paramDef['中文名称'],
                            data_type: paramDef['数据类型'],
                            example_description: paramDef['示例'],
                            measurement_class: paramDef['计量单位分类'],
                            data_source: paramDef['数据来源'],
                            is_required: paramDef['是否必填'] === '是',
                            parameter_value: '', // 初始值为空
                        });
                    }
                }

                // 插入位置参数
                const positionParams = getParameterDefinitions('位置信息表');
                for (const paramDef of positionParams) {
                    if (paramDef['中文名称'] && paramDef['中文名称'].trim() !== '') {
                        getDbInstance().insertPositionParameter({
                            device_id: newDevice.id,
                            parameter_name: paramDef['中文名称'],
                            data_type: paramDef['数据类型'],
                            example_description: paramDef['示例'],
                            measurement_class: paramDef['计量单位分类'],
                            data_source: paramDef['数据来源'],
                            is_required: paramDef['是否必填'] === '是',
                            parameter_value: '', // 初始值为空
                        });
                    }
                }
                
                // 插入基础参数
                const baseParams = getParameterDefinitions('基础参数表');
                for (const paramDef of baseParams) {
                    if (paramDef['中文名称'] && paramDef['中文名称'].trim() !== '') {
                        getDbInstance().insertBaseParameter({
                            device_id: newDevice.id,
                            parameter_name: paramDef['中文名称'],
                            data_type: paramDef['数据类型'],
                            example_description: paramDef['示例'],
                            measurement_class: paramDef['计量单位分类'],
                            data_source: paramDef['数据来源'],
                        });
                    }
                }
            }
        }
    }
    console.log(`[Importer] Successfully imported ${totalDevices} devices with their parameters into SQLite.`);
    return totalDevices;
}


export async function importAllDataFromCsv() {
  try {
    console.log('[Importer] Starting full data import process into SQLite...');
    const db = getDbInstance();
    
    db.exec('DELETE FROM devices');
    db.exec("DELETE FROM sqlite_sequence WHERE name='devices'");
    db.exec('DELETE FROM naming_parameters');
    db.exec("DELETE FROM sqlite_sequence WHERE name='naming_parameters'");
    db.exec('DELETE FROM position_parameters');
    db.exec("DELETE FROM sqlite_sequence WHERE name='position_parameters'");
    db.exec('DELETE FROM base_parameters');
    db.exec("DELETE FROM sqlite_sequence WHERE name='base_parameters'");
    db.exec('DELETE FROM measurement_units');
    db.exec("DELETE FROM sqlite_sequence WHERE name='measurement_units'");
    console.log('[Importer] All relevant SQLite tables cleared.');

    importMeasurementUnits();
    const importedCount = await importDevicesAndParameters();

    console.log('[Importer] Full data import process for devices and parameters completed successfully.');
    return { success: true, message: `成功导入 ${importedCount} 个设备及其所有参数。`, importedCount };
  } catch (error) {
    const message = error instanceof Error ? error.message : 'An unknown error occurred during import.';
    console.error(`[Importer] CSV Import to SQLite Failed: ${message}`);
    if (error instanceof Error && error.stack) {
        console.error(error.stack);
    }
    return { success: false, message };
  }
}
